-- @owner: @zou_jialiang050
-- @date: 2022/02/24
-- @testpoint: 结合带游标函数对触发器进行测试

--step1:建表插数据;expect:成功
drop table if exists t_ustore_trigger_case0071;
create table t_ustore_trigger_case0071 (id int,name varchar(50),age int) with (storage_type=ustore);
insert into t_ustore_trigger_case0071 values(1,'xi',20);
insert into t_ustore_trigger_case0071 values(2,'xw',22);
insert into t_ustore_trigger_case0071 values(3,'xh',24);
insert into t_ustore_trigger_case0071 values(4,'xz',26);

--step2:结合带游标函数对触发器进行测试;expect:成功
create or replace function f_ustore_trigger_case0071()
returns trigger as $$
declare
a int;
b varchar(50);
cursor c1 is select id,name from t_ustore_trigger_case0071;
begin
    open c1;
    loop
        fetch c1 into a,b;
        exit when c1%notfound;
    end loop;
    close c1;
		create table t_ustore_trigger_case0071_1 (id int,name varchar(50));
		insert into t_ustore_trigger_case0071_1 values(a,b);
	return new;
end;
$$language plpgsql;
/
create trigger tri_ustore_trigger_case0071 before update on t_ustore_trigger_case0071 for each row execute procedure f_ustore_trigger_case0071();
/
update t_ustore_trigger_case0071 set id =100 where id=4;
select * from t_ustore_trigger_case0071 order by id;
select * from t_ustore_trigger_case0071_1 order by id;

--step3:清理环境;expect:成功
drop trigger if exists tri_ustore_trigger_case0071 on t_ustore_trigger_case0071;
drop table if exists t_ustore_trigger_case0071;
drop table if exists t_ustore_trigger_case0071_1;
drop function if exists f_ustore_trigger_case0071;